package com.cc.excel.jxl;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringRunner;

import java.io.File;
import java.sql.*;


@RunWith(SpringRunner.class)
@ContextConfiguration("classpath:beans.xml")
public class Test2 {
	public static void main(String[] args) throws Exception {
	    Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://127.0.0.1:3306/java01?useUnicode=true&characterEncoding=utf8&useSSL=true";
        Connection conn = DriverManager.getConnection(url,"root","root");
        Statement st = conn.createStatement();
        String sql = "select * from stu_info";
        
        ResultSet rs = st.executeQuery(sql);
		//获取表格的原生对象
		ResultSetMetaData rsmd = rs.getMetaData();
		
		//从上面的这个对象中可以知道有多少个列
		int counts = rsmd.getColumnCount();
		//生成excel
		WritableWorkbook book = Workbook.createWorkbook(new File("studentinfo.xls"));
		WritableSheet sheet = book.createSheet("数据", 0);
		String [] columns = new String[counts];
		//添加表头
		for(int i=0;i<counts;i++){
			columns[i] = rsmd.getColumnName(i+1);
			sheet.addCell(new Label(i, 0, columns[i]));
		}
		
		//获取数据库表的内容，写到excel中去
		int j = 1;
		while(rs.next()){
			for(int i=0;i<counts;i++){
				sheet.addCell(new Label(i, j, rs.getString(columns[i])));
			}
			j++;
		}
		
		book.write();
		book.close();
		
		
	
		
		
		
		

	}

}
